/*import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import net.sf.json.JSONObject;
import net.sf.json.JSONSerializer;
import net.sf.json.JSONString;*/

//import org.json.simple.parser.JSONParser;
/*

//import org.json.JSONArray;
//import org.json.JSONObject;
//import org.json.JSONTokener;
//import org.json.JSONStringer;
import org.json.JSONTokener;
import org.json.simple.JSONArray;
//import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;*/
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Iterator;
import java.util.Map.Entry;

import com.google.gson.stream.JsonReader;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonIOException;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.google.gson.JsonSyntaxException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
 
public class jsontoexcel {
   static int mainId=1;
   static int secId =1;
   static int mobile_count=0;
   static int mainCol =1;
   static String fileName="";
   static WorkbookSettings wbSettings;
   static WritableWorkbook workbook = null;
  static WritableSheet nregaSheet=null;
  static WritableSheet phoneSheet =null;
   static int nrega_col=1;
   static int phone_col=1;
   private static void addCaption(WritableSheet sheet, int column, int row, String s)
   throws RowsExceededException, WriteException {
 Label label;
 label = new Label(column, row, s);
 sheet.addCell(label);
}

private static void addNumber(WritableSheet sheet, int column, int row,
   Integer integer) throws WriteException, RowsExceededException {
 Number number;
 number = new Number(column, row, integer);
 sheet.addCell(number);
}
private static void addDecimal(WritableSheet sheet, int column, int row,
		   double d) throws WriteException, RowsExceededException {
		 Number decimal1;
		 decimal1 = new Number(column, row, d);
		 sheet.addCell(decimal1);
		}

private static void addLabel(WritableSheet sheet, int column, int row, String s)
   throws WriteException, RowsExceededException {
 Label label;
 label = new Label(column, row, s);
 sheet.addCell(label);
}
	public static void writeExcel(String key, JsonElement val) throws RowsExceededException, WriteException
	{
	   String value;
		if(!val.isJsonNull())
	    {
		 value = val.getAsString();
	    }
	    else
	    	{
	    	value = "null";
	    	}
		if(key.equals("generated_table_list_label_2") )
		{
			
			
			if(mainId==1)
			{
				// print the keys and the first line in the excel
				try {
					addCaption(nregaSheet,0,0,"submissionId");
					
					addCaption(nregaSheet,nrega_col,0,key);
					
				} catch (RowsExceededException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (WriteException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			// print the value
			try {
				addNumber(nregaSheet,0,mainId,mainId);
				addLabel(nregaSheet,nrega_col,mainId,value);
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			nrega_col++;
		}
		
		else if(key.equals("instanceID"))
		{
			// write to excel main excel sheet
			if(mainId==1)
			{
				addCaption(nregaSheet,nrega_col,0,key);
			}
			addLabel(nregaSheet,nrega_col,mainId,value);
			nrega_col=1;
			mainId++;
		}
		else if(key.equals("mobilephones"))
		{
			Integer value1;
			if(!val.isJsonNull())
		    {
			  value1 = val.getAsInt();
		    }
			else 
				{value1=0;}
			if(mainId==1)
			{
				// write to main excel sheet with key
				addCaption(nregaSheet,nrega_col,0,key);
			}
			addNumber(nregaSheet,nrega_col,mainId,value1);
		    nrega_col++;
			
		}
		else if(key.equals("land_owned"))
		{
			Double value1;
			if(!val.isJsonNull())
		    {
			  value1 = val.getAsDouble();
		    }
			else 
				{value1=0.0;}
			if(mainId==1)
			{
				// write to main excel sheet with key
				addCaption(nregaSheet,nrega_col,0,key);
			}
			addDecimal(nregaSheet,nrega_col,mainId,value1);
		    nrega_col++;
			
		}
		else if(key.equals("belongs") )
		{
			//write to mobile excel sheet
			if(secId==1)
			{
				addCaption(phoneSheet,mobile_count+2,0,key);
			}
			addLabel(phoneSheet,mobile_count+2,secId,value);
			secId++;
			mobile_count=0;
		}
		else if(key.equals("mobileno"))
		{
			// write to mobile excel sheet
			if(secId==1)
			{
			
				
				addCaption(phoneSheet,0,0,"phoneId");	
				addCaption(phoneSheet,1,0,"submissionId");
				addCaption(phoneSheet,mobile_count+2,0,key);//write the headers and the first row 
			}
			
			addNumber(phoneSheet,0,secId,secId);
			addNumber(phoneSheet,1,secId,mainId);
			addLabel(phoneSheet,mobile_count+2,secId,value);
			mobile_count++;
		}
		
		
		else if(mobile_count>0)
		{
			// write to mobile excel sheet
			if(secId==1)
			{
				addCaption(phoneSheet,mobile_count+2,0,key);
			}
			addLabel(phoneSheet,mobile_count+2,secId,value);
			mobile_count++;
		}
		else
		{
			if(mainId==1)
			{
				// write to main excel sheet with key
				addCaption(nregaSheet,nrega_col,0,key);
			}
			addLabel(nregaSheet,nrega_col,mainId,value);
		    nrega_col++;	
		}
	
		
	}
	public static void readJSONFile(JsonObject jsonObject) throws RowsExceededException, WriteException
	{
		for (Entry<String, JsonElement> entry : jsonObject.entrySet()) {

	            String key = entry.getKey();
	            JsonElement value = entry.getValue();

	            if (value.isJsonObject()) {
	                readJSONFile(value.getAsJsonObject());
	            }
	            
	            else if (value.isJsonArray()) {
	                 JsonArray jsonArray = value.getAsJsonArray();
	            
	                 if (jsonArray.size() == 1) {
	                    readJSONFile((JsonObject) jsonArray.get(0));
	                }
	                 else if(key.equals("phone_usage"))
	                 {
	                	 for(int i=0;i<jsonArray.size();i++)
	                	 {
	                		  if(!jsonArray.get(i).isJsonNull())
	         	            {
	         	            
	                		 readJSONFile((JsonObject) jsonArray.get(i));
	         	            }
	                		  
	                	 }
	                	
	                 }
	                 else {
	                    //prints json array name
	                    System.out.println(key);
	                    Iterator<JsonElement> msg = jsonArray.iterator();
	                    while (msg.hasNext()) {
	                        ////prints json array values
	                        System.out.println(msg.next());
	                    }
	                }
	            }
	            else {
	                ////prints json object's keys and values
	               System.out.println(key + " - " + value);
	            	writeExcel(key,value);
	            }
	            
	        }
	}
	public void json_excel(String FileName) throws JsonIOException, JsonSyntaxException, WriteException, IOException {
		 JsonParser parser = new JsonParser();
		 JsonElement jsonElement= (JsonElement) parser.parse(new FileReader(FileName));
		 String ext[]= FileName.split("\\.");
		// fileName ="C:/Users/jatin/Desktop/Ghattu_Survey_Final_Updated_results1.xls";
		 fileName=ext[0]+".xls";
		 wbSettings = new WorkbookSettings();
		 File file = new File(fileName);
		 wbSettings.setLocale(new Locale("en", "EN"));
		  
			try {
				workbook = Workbook.createWorkbook(file, wbSettings);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    workbook.createSheet("NREGA", 0);
		    workbook.createSheet("Phone", 1);
		     nregaSheet = workbook.getSheet(0);
		     phoneSheet = workbook.getSheet(1);
		JsonObject jsonObject = jsonElement.getAsJsonObject();
        readJSONFile(jsonObject);
        workbook.write();
        workbook.close();
	    
		
		
	} 
	     
}
